package net.zoneland.knowledge.dao;

import java.util.List;

import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import net.zoneland.knowledge.model.MeetingQr;

/**
 * 会议(二维码)数据接口.
 * @author chw.
 * @version V1.0
 * 2022-11-17
 */
@Mapper
public interface MeetingQrMapper {

    /**
     * 根据会议id获取会议信息.
     * @param id 会议id
     * @return 会议信息
     */
    @Select({
        "SELECT ID, MEETING_TITLE, MEETING_DATE, MEETING_CONTENT, MEETING_ADDRESS, MEETING_QR_CODE, CREATOR_ID, ",
        "CREATOR_NAME, DEPARTMENT_DN, COMPANY_NAME, COMPANY_ID, CREATE_TIME, LAST_MODIFIED,MEETING_END_DATE ",
        "FROM AITK_MEETING_QR WHERE ID = #{id, jdbcType = VARCHAR} "
    })
    @Results(id = "meetingQr", value = {
        @Result(column="ID", property="id", jdbcType= JdbcType.VARCHAR, id=true),
        @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_DATE", property="meetingDate", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="MEETING_CONTENT", property="meetingContent", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_ADDRESS", property="meetingAddress", jdbcType=JdbcType.VARCHAR),
        @Result(column="MEETING_QR_CODE", property="meetingQrCode", jdbcType=JdbcType.BLOB),
        @Result(column="CREATOR_ID", property="creatorId", jdbcType=JdbcType.VARCHAR),
        @Result(column="CREATOR_NAME", property="creatorName", jdbcType=JdbcType.VARCHAR),
        @Result(column="DEPARTMENT_DN", property="departmentDn", jdbcType=JdbcType.VARCHAR),
        @Result(column="COMPANY_NAME", property="companyName", jdbcType=JdbcType.VARCHAR),
        @Result(column="COMPANY_ID", property="companyId", jdbcType=JdbcType.VARCHAR),
        @Result(column="CREATE_TIME", property="createTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="LAST_MODIFIED", property="lastModified", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="MEETING_END_DATE", property="meetingEndDate", jdbcType=JdbcType.TIMESTAMP),
    })
    MeetingQr search(final String id);

    /**
     * 更新会议签到人数.
     * @param id 会议id
     */
    @Update({
            "update AITK_MEETING_QR ",
            "set SIGN_COUNT = (SELECT COUNT(1) FROM AITK_MEETING_SIGN_IN WHERE MEETING_ID = #{id, jdbcType = VARCHAR} and SIGN_IN_DATE is NOT NULL) ",
            "WHERE ID = #{id, jdbcType = VARCHAR} "
    })
    void updateMeetingSignCount(String id);

    /**
     * 根据用户获取当前日期之后的会议信息.
     * @param userId 用户id
     * @return 会议列表
     */
    @Select({
            "SELECT ID, MEETING_TITLE, MEETING_DATE, MEETING_CONTENT, MEETING_ADDRESS, MEETING_QR_CODE, CREATOR_ID, ",
            "CREATOR_NAME, DEPARTMENT_DN, COMPANY_NAME, COMPANY_ID, CREATE_TIME, LAST_MODIFIED,MEETING_END_DATE ",
            "FROM AITK_MEETING_QR t WHERE MEETING_END_DATE > sysdate and exists ",
            "(select 1 from aitk_meeting_sign_in where meeting_id = t.id and sign_in_user_id = #{userId, jdbcType = VARCHAR})"
    })
    @ResultMap(value = {"meetingQr"})
    List<MeetingQr> findNotFinishMeetingByUserId(@Param("userId") String userId);

    /**
     * 根据会议id，修改会议状态进行软删除
     * @param id 会议id
     * @return 删除条数
     */
    @Update({"UPDATE AITK_MEETING_QR SET STATUS = #{status} WHERE ID = #{id, jdbcType = VARCHAR}"})
    int editMeetingQrStatus(final @Param("id") String id, final @Param("status") boolean status);

    /**
     * 根据创建人id查询创建的会议
     * @param creatorId 创建人id
     * @return 创建会议集合
     */
    @Select({
            "SELECT MEETING_TITLE, MEETING_CONTENT, MEETING_DATE, MEETING_ADDRESS, IMPORT_COUNT, SIGN_COUNT, ID ",
            "FROM AITK_MEETING_QR ",
            "WHERE CREATOR_ID = #{creatorId} AND STATUS = 0 ORDER BY MEETING_DATE DESC"
    })
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_CONTENT", property="meetingContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_DATE", property="meetingDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_ADDRESS", property="meetingAddress", jdbcType=JdbcType.VARCHAR),
            @Result(column="IMPORT_COUNT", property="importCount", jdbcType=JdbcType.INTEGER),
            @Result(column="SIGN_COUNT", property="signCount", jdbcType=JdbcType.INTEGER),
            @Result(column="CREATOR_ID", property="creatorId", jdbcType=JdbcType.VARCHAR),
            @Result(column="STATUS", property="status", jdbcType=JdbcType.INTEGER),
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR),
    })
    List<MeetingQr> queryMeetingByCreateId(final @Param("creatorId") String creatorId);

    /**
     * 根据会议签到表的meetingId查询会议二维码表的id
     * @param meetingIdResult 会议id.
     * @return
     */
    @Select({"SELECT MEETING_CONTENT, MEETING_ADDRESS, MEETING_DATE, MEETING_TITLE,ID FROM AITK_MEETING_QR WHERE ID = #{id, jdbcType = VARCHAR} AND STATUS = 0"})
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_CONTENT", property="meetingContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_DATE", property="meetingDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_ADDRESS", property="meetingAddress", jdbcType=JdbcType.VARCHAR),
            @Result(column="STATUS", property="status", jdbcType=JdbcType.INTEGER),
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR),
    })
    List<MeetingQr> queryIdByMeetingId(final @Param("id") String meetingIdResult);

    /**
     * 查询参会二维码
     * @param id 主键id
     * @return 会议二维码对象
     */
    @Select({"SELECT MEETING_TITLE, MEETING_QR_CODE FROM AITK_MEETING_QR WHERE ID = #{id, jdbcType = VARCHAR} AND STATUS = 0"})
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.BLOB),
            @Result(column="MEETING_QR_CODE", property="meetingQrCode", jdbcType=JdbcType.BLOB),
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR),
            @Result(column="STATUS", property="status", jdbcType=JdbcType.INTEGER),
    })
    MeetingQr queryMeetingQrCode(final @Param("id") String id);


    /**
     * 会议详情
     * @param id 会议(二维码).
     * @return
     */
    @Select({"SELECT a.ID, a.MEETING_TITLE, a.MEETING_CONTENT, a.MEETING_DATE, a.MEETING_ADDRESS, a.MEETING_END_DATE, a.IMPORT_COUNT, a.SIGN_COUNT, a.PHONE_REMIND, a.SMS_REMIND, a.SMS_ADVANCE_SEND, a.PHONE_ADVANCE_SEND,",
            " a.SMS_CONTENT, a.PHONE_CONTENT FROM AITK_MEETING_QR a WHERE a.ID = #{id, jdbcType = VARCHAR} AND a.STATUS = 0"})
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_CONTENT", property="meetingContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_DATE", property="meetingDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_ADDRESS", property="meetingAddress", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_END_DATE", property="meetingEndDate", jdbcType=JdbcType.VARCHAR),
            @Result(column="IMPORT_COUNT", property="importCount", jdbcType=JdbcType.INTEGER),
            @Result(column="SIGN_COUNT", property="signCount", jdbcType=JdbcType.INTEGER),
            @Result(column="STATUS", property="status", jdbcType=JdbcType.INTEGER),
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR),
            @Result(column="PHONE_REMIND", property="phoneRemind", jdbcType=JdbcType.INTEGER),
            @Result(column="SMS_REMIND", property="smsRemind", jdbcType=JdbcType.INTEGER),
            @Result(column="SMS_ADVANCE_SEND", property="smsAdvanceSend", jdbcType=JdbcType.INTEGER),
            @Result(column="PHONE_ADVANCE_SEND", property="phoneAdvanceSend", jdbcType=JdbcType.INTEGER),
            @Result(column="SMS_CONTENT", property="smsContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="PHONE_CONTENT", property="phoneContent", jdbcType=JdbcType.VARCHAR),
    })
    MeetingQr queryMeetingContent(final @Param("id") String id);


    /**
     * 更新会议信息
     * @param meeting
     */
    @Update({"update AITK_MEETING_QR set MEETING_TITLE = #{meetingTitle, jdbcType = VARCHAR}, MEETING_DATE = #{meetingDate, jdbcType = TIMESTAMP}, " +
            "MEETING_END_DATE = #{meetingEndDate, jdbcType = TIMESTAMP}, MEETING_CONTENT = #{meetingContent, jdbcType = VARCHAR}, MEETING_ADDRESS = #{meetingAddress, jdbcType = VARCHAR}," +
            "PHONE_REMIND = #{phoneRemind, jdbcType = INTEGER}, PHONE_CONTENT = #{phoneContent, jdbcType = VARCHAR}, PHONE_ADVANCE_SEND =#{phoneAdvanceSend, jdbcType = INTEGER}," +
            "SMS_REMIND = #{smsRemind, jdbcType = INTEGER}, SMS_ADVANCE_SEND =#{smsAdvanceSend, jdbcType = INTEGER}, SMS_CONTENT =#{smsContent, jdbcType = VARCHAR} " +
            "WHERE ID = #{id, jdbcType = VARCHAR}"})
    int saveOrUpdate(final MeetingQr meeting);

    /**
     * 保存会议信息
     * @param meetingQr
     */
    @Insert({
            "insert into AITK_MEETING_QR (CREATOR_ID, CREATOR_NAME, COMPANY_NAME, DEPARTMENT_DN, CREATE_TIME, ",
            "MEETING_TITLE, MEETING_DATE, MEETING_END_DATE, MEETING_CONTENT, MEETING_ADDRESS, PHONE_REMIND, ",
            "PHONE_CONTENT, PHONE_ADVANCE_SEND, SMS_REMIND, SMS_ADVANCE_SEND, SMS_CONTENT, ID, MEETING_QR_CODE",
            ") ",
            "values (#{creatorId, jdbcType = VARCHAR}, #{creatorName, jdbcType = VARCHAR}, #{companyName, jdbcType = VARCHAR}, ",
            "#{departmentDn, jdbcType = VARCHAR}, #{createTime, jdbcType = VARCHAR}, #{meetingTitle, jdbcType = VARCHAR}, ",
            "#{meetingDate, jdbcType = TIMESTAMP}, #{meetingEndDate, jdbcType = TIMESTAMP}, #{meetingContent, jdbcType = VARCHAR}, ",
            "#{meetingAddress, jdbcType = VARCHAR}, #{phoneRemind, jdbcType = INTEGER}, #{phoneContent, jdbcType = VARCHAR}, ",
            "#{phoneAdvanceSend, jdbcType = INTEGER}, #{smsRemind, jdbcType= INTEGER}, #{smsAdvanceSend, jdbcType = INTEGER}, ",
            "#{smsContent, jdbcType = VARCHAR}, #{id, jdbcType = VARCHAR}, #{meetingQrCode, jdbcType = BLOB})"
    })
    int save(final MeetingQr meetingQr);

    /**
     * 根据meeting更新会议人员数量
     * @param meetingQr 会议二维码实体类
     */

    @Update({"update AITK_MEETING_QR set IMPORT_COUNT = #{importCount, jdbcType = INTEGER} WHERE ID = #{id, jdbcType = VARCHAR}"})
    void updateMeetingQrStaffByMeetingId(final MeetingQr meetingQr);

    /**
     * 根据id更新参会人数
     * @param id 会议id
     */
    @Update({
            "update AITK_MEETING_QR set IMPORT_COUNT = (select count(1) from AITK_MEETING_SIGN_IN where MEETING_ID = #{id, jdbcType = VARCHAR}) ",
            "WHERE ID = #{id, jdbcType = VARCHAR}"
    })
    void updateMeetingById(final String id);
    /**
     * 根据meetingId获取数据.
     * @param id 会议Id
     * @return
     */
    @Select({"SELECT ID, MEETING_TITLE, IMPORT_COUNT FROM AITK_MEETING_QR WHERE ID = #{id, jdbcType = VARCHAR}"})
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
            @Result(column="IMPORT_COUNT", property="importCount", jdbcType=JdbcType.VARCHAR),
    })
    MeetingQr getParamByMeetingId(final @Param("id") String id);

    @Select({"<script>",
            "SELECT ID, MEETING_TITLE, MEETING_DATE, MEETING_END_DATE, MEETING_CONTENT, MEETING_ADDRESS, IMPORT_COUNT, SIGN_COUNT ",
            "FROM AITK_MEETING_QR ",
            "where STATUS = 0 ",
            "AND CREATOR_ID = #{creatorId, jdbcType = VARCHAR} ",
            "<if test=\"meetingTitle != null and meetingTitle != ''\">",
            "AND MEETING_TITLE LIKE CONCAT(CONCAT('%', #{meetingTitle, jdbcType=VARCHAR}), '%') ",
            "</if>",
            "ORDER BY MEETING_DATE DESC",
            "</script>"})
    @Results({
            @Result(column="MEETING_TITLE", property="meetingTitle", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_CONTENT", property="meetingContent", jdbcType=JdbcType.VARCHAR),
            @Result(column="MEETING_DATE", property="meetingDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_END_DATE", property="meetingEndDate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="MEETING_ADDRESS", property="meetingAddress", jdbcType=JdbcType.VARCHAR),
            @Result(column="IMPORT_COUNT", property="importCount", jdbcType=JdbcType.INTEGER),
            @Result(column="SIGN_COUNT", property="signCount", jdbcType=JdbcType.INTEGER),
            @Result(column="STATUS", property="status", jdbcType=JdbcType.INTEGER),
            @Result(column="CREATOR_ID", property="creatorId", jdbcType=JdbcType.VARCHAR),
            @Result(column="ID", property="id", jdbcType=JdbcType.VARCHAR),
    })
    List<MeetingQr> queryPager(final MeetingQr meetingQr);

    /**
     * 更新参会人员数量
     * @param id 会议id
     */
    @Update({
            "update AITK_MEETING_QR ",
            "set IMPORT_COUNT = (SELECT COUNT(1) FROM AITK_MEETING_SIGN_IN WHERE MEETING_ID = #{id, jdbcType = VARCHAR}) ",
            "WHERE ID = #{id, jdbcType = VARCHAR} "
    })
    void updateMeetingImportCount(String id);
}
